package bpm.dao.impl.manage;

import java.util.List;
import javax.annotation.Resource;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import bpm.dao.manage.DictionaryDao;
import bpm.domain.common.PageObject;
import bpm.domain.manage.Dictionary;
@Repository("dictionaryDao")
public class DictionaryDaoImpl implements DictionaryDao {
	@Resource private JdbcTemplate jdbcTemplate;
	private static final Logger logger =Logger.getLogger(DictionaryDaoImpl.class);
	public void delete(String[] dictionaryIds) {
		StringBuffer sql=new StringBuffer();
		sql.append(" delete from BPM_AD_DICTIONARY where ID in (");
		for (int i = 0; i < dictionaryIds.length; i++) {
			if(i!=dictionaryIds.length-1){
				sql.append("?,");
			}else{
				sql.append("? )");
			}
		}
		logger.debug("DeleteSQL:"+sql.toString());
		this.jdbcTemplate.update(sql.toString(),dictionaryIds);
	}
	@SuppressWarnings("unchecked")
	public List<Dictionary> findDict(Dictionary dict, PageObject pageObject) {
		String code=dict.getCode();
		String dictName=dict.getName();
		String dictTypeId=dict.getDictionaryType()==null?null:dict.getDictionaryType().getId();
		StringBuffer count=new StringBuffer();
		StringBuffer sql=new StringBuffer();
		count.append(" select count(*) from BPM_AD_DICTIONARY ");
		sql.append(" select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION ")
			.append(" from ( select rownumber() over(order by SORT_INDEX asc ) as num ,")
			.append("  ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from " )
			.append(" BPM_AD_DICTIONARY ");
			
		if(code!=null&&!"".equals(code)||dictTypeId!=null&&!"".equals(dictTypeId)||dictName!=null&&!"".equals(dictName)){
			count.append(" where ");
			sql.append(" where ");
			if(code!=null &&!"".equals(code)){
				count.append(" CODE like '%" ).append(code).append("%' and ");
				sql.append(" CODE like '%" ).append(code).append("%' and ");
			}
			if(dictTypeId!=null&&!"".equals(dictTypeId)){
				count.append(" TYPE_ID = '").append(dictTypeId).append("' and ");
				sql.append(" TYPE_ID = '").append(dictTypeId).append("' and ");
			}
			if(dictName!=null&&!"".equals(dictName)){
				count.append(" NAME like '%").append(dictName).append("%' and ");
				sql.append(" NAME like '%").append(dictName).append("%' and ");
			}
			String countTemp=count.substring(0, count.lastIndexOf("and"));
			count=new StringBuffer();
			count.append(countTemp);
			String sqlTemp=sql.substring(0,sql.lastIndexOf("and"));
			sql=new StringBuffer();
			sql.append(sqlTemp);
		}
		sql.append(") where num> ? and num<=? ");
		
		PageObject.setPageObject(jdbcTemplate, count.toString(), pageObject);
		return this.jdbcTemplate.query(sql.toString(), new Object[]{pageObject.getStart(),pageObject.getEnd()},
				new DictionaryRowMapper());
	}

	public Dictionary findDictById(String dictId) {
		String sql="select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from BPM_AD_DICTIONARY where "
			+" ID=?";
		logger.debug("QuerySQL:"+sql);
		List<?> list= this.jdbcTemplate.query(sql, new Object[]{dictId},new DictionaryRowMapper());
		if(list!=null&&list.size()>0){
			return (Dictionary)list.get(0);
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public List<Dictionary> findDictByTypeId(String typeId,
			PageObject pageObject) {
			if(typeId!=null&&!"".equals(typeId)){
			StringBuffer count=new StringBuffer();
			StringBuffer sql=new StringBuffer();
			sql.append(" select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from (")
				.append(" select rownumber()over(order by SORT_INDEX asc ) as num,ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from ")
			    .append(" BPM_AD_DICTIONARY where TYPE_ID=? ) where num>? and num<=?");
			count.append(" select count(*) from BPM_AD_DICTIONARY where TYPE_ID='").append(typeId).append("' ");
			PageObject.setPageObject(jdbcTemplate, count.toString(), pageObject);
			logger.debug("CountSQL:"+count.toString());
			logger.debug("QuerySQL:"+sql.toString());
			return this.jdbcTemplate.query(sql.toString(),new Object[]{typeId,pageObject.getStart(),pageObject.getEnd()}, new DictionaryRowMapper());
		}else{
			StringBuffer count=new StringBuffer();
			StringBuffer sql=new StringBuffer();
			sql.append(" select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from (")
				.append(" select rownumber()over(order by SORT_INDEX asc ) as num,ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from ")
			    .append(" BPM_AD_DICTIONARY ) where num>? and num<=?");
			count.append(" select count(*) from BPM_AD_DICTIONARY ");
			PageObject.setPageObject(jdbcTemplate, count.toString(), pageObject);
			logger.debug("CountSQL:"+count.toString());
			logger.debug("QuerySQL:"+sql.toString());
			return this.jdbcTemplate.query(sql.toString(),new Object[]{pageObject.getStart(),pageObject.getEnd()}, new DictionaryRowMapper());
		}
	}

	public void insert(Dictionary dictionary) {
		StringBuffer sql=new StringBuffer();
		sql.append(" insert into BPM_AD_DICTIONARY (ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION ) values (?,?,?,?,?,?,?,?,?)");
		logger.debug("InsertSQL:"+sql.toString());
		this.jdbcTemplate.update(sql.toString(), new Object[]{
			dictionary.getId(),dictionary.getCode(),
			dictionary.getName(),dictionary.getPCode(),
			dictionary.getDictionaryType().getId(),dictionary.getSortIndex(),
			new java.sql.Date(dictionary.getCreateTime().getTime()),
			new java.sql.Date(dictionary.getLastUpdateTime().getTime()),
			dictionary.getDescription()
		});
	}

	public void update(Dictionary dictionary) {
		StringBuffer sql=new StringBuffer();
		sql.append(" update BPM_AD_DICTIONARY set CODE=?,name=?,SORT_INDEX=?,LASTUPDATE_TIME=?,DESCRIPTION=? where ID=?");
		logger.debug("UpdateSQL:"+sql.toString());
		this.jdbcTemplate.update(sql.toString(),new Object[]{
			dictionary.getCode(),
			dictionary.getName(),dictionary.getSortIndex(),
			new java.sql.Date(dictionary.getLastUpdateTime().getTime()),
			dictionary.getDescription(),dictionary.getId()
		});
	}
	@SuppressWarnings("unchecked")
	public List<Dictionary> findDictionarysByPidAndType(Dictionary dict) {
		StringBuffer sql=new StringBuffer();
		sql.append(" with dict1(ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME, LASTUPDATE_TIME, DESCRIPTION) as ( ")
			.append(" select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME, LASTUPDATE_TIME, DESCRIPTION ")
			.append(" from BPM_AD_DICTIONARY where  TYPE_ID=? and  PCODE=? ")
			.append(" union all ")
			.append(" select a.ID, a.CODE, a.NAME, a.PCODE, a.TYPE_ID, a.SORT_INDEX, a.CREATE_TIME, a.LASTUPDATE_TIME, a.DESCRIPTION  ")
			.append("  from BPM_AD_DICTIONARY as a , dict1 as b where a.PCODE=b.CODE  ")
			.append(" )select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME, LASTUPDATE_TIME, DESCRIPTION from dict1 order by SORT_INDEX asc ");
		logger.debug("WithSQL:"+sql.toString());
		return this.jdbcTemplate.query(sql.toString(),new Object[]{dict.getDictionaryType().getId(),dict.getPCode()}, new DictionaryRowMapper());
	}
	public Integer findMaxCode(Dictionary dictionary) {
		StringBuffer sql=new StringBuffer();
		sql.append(" select max(CODE) from BPM_AD_DICTIONARY where  TYPE_ID=? and  PCODE=? ") ;
		logger.debug("QuerySQL:"+sql.toString());
		return this.jdbcTemplate.queryForInt(sql.toString(),new Object[]{dictionary.getDictionaryType().getId(),dictionary.getPCode()});
	}
	@SuppressWarnings("unchecked")
	public List<Dictionary> findDictionaryByName(Dictionary dictionary) {
		StringBuffer sql=new StringBuffer();
		 sql.append("select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from BPM_AD_DICTIONARY where ")
		 	.append(" (TYPE_ID,NAME)=(?,?)");
		return this.jdbcTemplate.query(sql.toString(), new Object[]{dictionary.getDictionaryType().getId(),dictionary.getName()}, new DictionaryRowMapper());
	}
	
	@SuppressWarnings("unchecked")
	public List<Dictionary> findAll() {
		String sql = "select ID, CODE, NAME, PCODE, TYPE_ID, SORT_INDEX, CREATE_TIME,LASTUPDATE_TIME, DESCRIPTION  from BPM_AD_DICTIONARY  order by  TYPE_ID,CODE  ";
		return this.jdbcTemplate.query(sql, new DictionaryRowMapper());
	}

}
